package com.testmanager.testmananger.shuiyou.common.util;

import com.testmanager.testmananger.shuiyou.common.myAnnotation.MyColumn;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class ExcelOperte{

    InputStream inputStream = null;
    XSSFWorkbook xssfWorkbook = null;

    public ExcelOperte(){

    }
    public ExcelOperte (InputStream inputStream) throws IOException {
        this.xssfWorkbook = new XSSFWorkbook(inputStream);
    }

    public ExcelOperte (File file) throws IOException, InvalidFormatException {
        this.xssfWorkbook = new XSSFWorkbook(file);
    }


    public <T> List<T> getSheet(int sheetNum,Class<T> clazz) throws Exception {
        XSSFSheet sheetAt = xssfWorkbook.getSheetAt(sheetNum);
        List<T> list = new ArrayList<>();
        if (null == sheetAt){
            throw new Exception("sheet is null,sheetnum:"+sheetNum);
        }
        int lastRowNum = sheetAt.getLastRowNum();
        XSSFRow row;
        short lastCellNum;
        XSSFCell cell;
        for (int i=0;i<=lastRowNum;i++){
            if (!rowFilter(sheetAt.getRow(i))) continue;
            T t = getRowContent(sheetAt.getRow(i),clazz);
            list.add(t);
        }
        return list;

    }

    public <T> T getRowContent(XSSFRow row,Class<T> clazz) throws Exception {

        Field[]  fields = clazz.getDeclaredFields();
        T bean = clazz.newInstance();
        boolean accessible = false;
        for (Field field : fields){
            accessible = field.isAccessible();
            boolean flag = field.isAnnotationPresent(MyColumn.class);
            if (!flag)continue;
            int cellnum = field.getAnnotation(MyColumn.class).cellNum();
           // field.getType()
            XSSFCell cell = row.getCell(cellnum);
            Object value=null;
            String stringCellValue = cell.getStringCellValue();
            Class o = field.getType();
            if (Objects.equals(o,int.class)){
                value = Integer.valueOf(stringCellValue);
            }
            if (Objects.equals(o,long.class)){
                value = Integer.valueOf(stringCellValue);
            }
            if (Objects.equals(o,String.class)){
                value = stringCellValue;
            }


            field.setAccessible(true);
            field.set(bean,value);
            field.setAccessible(accessible);
            // map.put(field.getName(),getdbResult(resultSet,field));
            // field.set
        }
        return bean;

    }



    private boolean rowFilter(XSSFRow row){
       // row.get
        if (row == null){
            return false;
        }
        if (row.getRowNum() == 0){
            return false;
        }

        if (row.getCell(0).getStringCellValue().startsWith("#")){
            return false;
        }

        return true;



    }



    public  <T> HSSFWorkbook createSheet(HSSFWorkbook workbook1, List<T> list, String name) throws IllegalAccessException {

        HSSFWorkbook workbook = workbook1;
        HSSFSheet sheet = workbook.createSheet(name);
        if (list.size()<1){
            return  workbook;
        }
        List<String> headers = getHeaders(list.get(0));
        HSSFRow headerRow = sheet.createRow(0);

        for (int i =0;i<headers.size();i++){
            HSSFCell cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
        }

        for (int i=0;i<list.size();i++){
            HSSFRow row = sheet.createRow(i + 1);
            setRow(row, headers, list.get(i));

        }
        return workbook;



    }

    private  <T> HSSFRow setRow(HSSFRow row1 , List<String> headers,T t) throws IllegalAccessException {
        HSSFRow row = row1;
        Field[] fields = t.getClass().getDeclaredFields();
        for (Field field : fields){
            field.setAccessible(true);
            int position = -1;
            for (int i =0 ;i<headers.size();i++){
                if (field.getName().equals(headers.get(i))){
                    position = i;
                    break;
                }
            }
            HSSFCell cell = row.createCell(position);
            if (null == field.get(t)){
                cell.setCellValue("");
            }else {
                cell.setCellValue(field.get(t).toString());
            }

        }

        return row;
    }




    private  <T> List<String> getHeaders(T t){
        List<String> headers = new ArrayList<>();
        Field[] declaredFields = t.getClass().getDeclaredFields();
        for (Field field : declaredFields){
            field.setAccessible(true);
            headers.add(field.getName());
        }
        return headers;
    }


}


